#!/usr/bin/env python3.6
# -*- coding: utf-8 -*-
# @Time    : 2020/9/18 15:29
# @File    : OperaMssql
# @Author  : 
# @Email   : 
# @remark  :操作MSSQL的基本类
import pymssql


class MSSQL:
    def __init__(self, host, port, user, pwd, db):
        self.host = host
        self.port = port
        self.user = user
        self.pwd = pwd
        self.db = db

    # 连接数据库
    def GetConnect(self):
        if not self.db:
            raise (NameError, '没有目标数据库')
        self.connect = pymssql.connect(host=self.host, port=self.port, user=self.user, password=self.pwd,
                                       database=self.db, charset='utf8')
        cur = self.connect.cursor()
        if not cur:
            raise (NameError, '数据库访问失败')
        else:
            return cur

    # 添加、更新、删除
    def ExecSql(self, sql):
        cur = self.GetConnect()
        cur.execute(sql)
        self.connect.commit()
        self.connect.close()

    # 查询语句
    def ExecQuery(self, sql):
        cur = self.GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()
        self.connect.close()
        return resList

    def ExecSqlList(self,sqlList):
        cur = self.GetConnect()
        try:
            for sql in sqlList:
                cur.execute(sql)
        except Exception as e:
            self.connect.rollback()  # 事务回滚
            print('事务处理失败', e)
        else:
            self.connect.commit()  # 事务提交
            print('事务处理成功', cur.rowcount)
        self.connect.close()
        return cur.rowcount


'''调用存储教程示例
with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute("""
        CREATE PROCEDURE FindPerson
            @name VARCHAR(100)
        AS BEGIN
            SELECT * FROM persons WHERE name = @name
        END
        """)
        cursor.callproc('FindPerson', ('Jane Doe',))
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))
'''